package com.ymt.fmd.platform.service;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.net.URLConnection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.logging.Logger;

import com.ymt.fmd.appboot.prop.FmdProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ymt.fmd.platform.dao.mysql.PBoMapper;
import com.ymt.fmd.platform.model.PBo;
import com.ymt.fmd.util.Const;
import com.ymt.fmd.util.DateUtil;
import com.ymt.fmd.util.DhxUtil;


@EnableConfigurationProperties(value = {FmdProperties.class})
@Service(value="fmdBoService")
public class BoService {
	
	private Logger logger = Logger.getLogger(BoService.class.getName());
	
	@Autowired
	private PBoMapper boDao;

	@Autowired
	private FmdProperties fmdProperties;

	public String get(String boid, boolean check, String lang, int timezoneOffset) {
		logger.finer("get boid="+boid);
		PBo bo = boDao.selectByPrimaryKey(boid);
		logger.finer("get bo="+bo);
		JSONObject attrs = JSONObject.parseObject(bo.getAttrs());//turn string to object
		JSONObject bojo = DhxUtil.toJsonWithDate(bo, lang, timezoneOffset);
		bojo.put("attrs", attrs);
		if (check) {
			/*String checked = attach(boid, check);
			JSONObject checkedJo = JSONObject.parseObject(checked);
			boolean exists = checkedJo.optBoolean("exists");
			if (exists) {
				JSONArray cols = checkedJo.getJSONArray("columns");
			}*/
		}
		return bojo.toString();
	}
	
	/**
	 * Get bo by boname
	 * @param boname
	 * @param lang
	 * @param timezoneOffset
	 * @return
	 */
	public String getByName(String tenantId, String boname, String lang, int timezoneOffset) {
		logger.finer("get boname="+boname);
		PBo bo = boDao.selectByBoname(tenantId, boname);
		logger.finer("get bo="+bo);
		JSONObject attrs = JSONObject.parseObject(bo.getAttrs());//turn string to object
		JSONObject bojo = DhxUtil.toJsonWithDate(bo, lang, timezoneOffset);
		bojo.put("attrs", attrs);
		return bojo.toString();
	}
	
	public String save(String tenantId, String boStr, String loginName, String displayName, String lang) {
		logger.finer("saving bostr="+boStr);
		JSONObject boJo = JSONObject.parseObject(boStr);
		String boid = boJo.getString("boid");
		if (boid==null || boid.length()==0) {
			PBo bo = new PBo();
			boid = "BO-"+UUID.randomUUID().toString();
			if (tenantId!=null) {
				bo.setTenantId(Integer.parseInt(tenantId));
			}
			bo.setBoid(boid);
			bo.setBoname(boJo.getString("boname"));
			String bodesc = boJo.getString("bodesc");
			if (bodesc!=null && bodesc.length()>0) {
				bo.setBodesc(bodesc);
			}
			bo.setPurpose(boJo.getString("purpose"));
			bo.setCreator(loginName);
			bo.setCreatorname(displayName);
			bo.setChangeuser(loginName);
			bo.setChangeusername(displayName);
			bo.setCreatetime(DateUtil.getGMTDate());
			bo.setUpdatetime(DateUtil.getGMTDate());
			bo.setAttrs(boJo.getJSONObject("attrs").toString());
			boDao.insert(bo);
		} else {
			PBo bo = boDao.selectByPrimaryKey(boid);
			String boname = boJo.getString("boname");
			if (boname!=null && boname.length()>0) {
				bo.setBoname(boname);
			}
			String bodesc = boJo.getString("bodesc");
			if (bodesc!=null && bodesc.length()>0) {
				bo.setBodesc(bodesc);
			}
			bo.setPurpose(boJo.getString("purpose"));
			bo.setChangeuser(loginName);
			bo.setChangeusername(displayName);
			bo.setUpdatetime(DateUtil.getGMTDate());
			JSONObject attrs = boJo.getJSONObject("attrs");
			if (attrs!=null) {
				bo.setAttrs(attrs.toString());
			}
			boDao.updateByPrimaryKeySelective(bo);
			boid = bo.getBoid();
		}
		return boid;
	}
	
	public int delete(String boid) {
		logger.finer("delete boid="+boid);
		return boDao.deleteByPrimaryKey(boid);
	}
	
	@SuppressWarnings("unchecked")
	public String query(String tenantId, String queryParams, int fInd, int lInd, int timezoneOffset, String lang, String ordercolumns) {
		logger.finer("query queryParams="+queryParams);
		Map<String, Object> params = new HashMap<String,Object>();
		Map<String, Object> bo = new HashMap<String,Object>();
		if (queryParams!=null) {
			bo.putAll(JSONObject.parseObject(queryParams));
		}
		if (tenantId!=null) {
			params.put("tenantId", tenantId);
		}
		params.put("bo", bo);
		params.put("fromRow", fInd-1);
		params.put("toRow", lInd);
		params.put("rows", lInd - fInd + 1);
		params.put("timezoneOffset", timezoneOffset);
		params.put("orderby", DhxUtil.parseGridOrderBy(ordercolumns));
		logger.finer("query params="+params);
		List<Map<String,?>> list = boDao.pagingSelect(params);
		int total = Integer.parseInt(boDao.pagingSelectCount(params).get("COUNT").toString());
		/*String[] cols = new String[]{"boid","boname","bodesc","purpose","updatetime","changeuser","changeusername",
				"creator","creatorname","createtime"};*/
		String[] cols = new String[]{"BOID","BONAME","BODESC","PURPOSE","UPDATETIME","CHANGEUSER",
				"CHANGEUSERNAME","CREATOR","CREATORNAME","UPDATETIME"};
		if (total > 0) {//解决linux mysql大小写问题
			if (!list.get(0).containsKey(cols[0]) && list.get(0).containsKey(cols[0].toLowerCase())) {
				for (int i=0; i<cols.length; i++) {
					cols[i] = cols[i].toLowerCase();
				}
			}
		}
		String rtn= DhxUtil.toGridJsonStr(list, 
				cols, fInd, total, timezoneOffset, lang);
		logger.finer("query returns:\n"+rtn);
		return rtn;
	}
	
	public String attach(String boid, boolean checkOnly) {
		String serverUrl = fmdProperties.getUrlModelreceiver();
		logger.finer("attach serverUrl="+boid);
		PBo bo = boDao.selectByPrimaryKey(boid);
		JSONObject param = new JSONObject();
		param.put("bo", bo);
		if (checkOnly) {
			param.put("checkOnly", checkOnly);
		}
		// 创建一个新的URL实例url
		try {
			URL url = new URL(serverUrl);
			// 根据url建立连接
			URLConnection con = url.openConnection();
			
			// 设置该连接可写
			con.setDoOutput(true);
			// 禁用cache
			con.setUseCaches(false);
			// 取出输出流
			OutputStream out = con.getOutputStream();
	        out.write(param.toString().getBytes());
			out.flush();
			out.close();
			
			// 取得返回的输入流
			InputStream rin = con.getInputStream();
			ByteArrayOutputStream baos = new ByteArrayOutputStream();  
			int i;  
			while ((i = rin.read()) != -1) {  
			    baos.write(i);  
			}  
			String rtn = baos.toString();
			baos.flush();
			baos.close();
			rin.close();
			
			return rtn;
		} catch (Exception e) {
			e.printStackTrace();
			return "{\"success\":false, \"err\":\"Local error:"+e.getMessage()+"\"}";
		}
		
	}
	
	public String isNameExists(String tenantId, String boname, String boid) {
		List<PBo> list = boDao.checkBoname(tenantId, boname, boid);
		logger.finer("get list="+list);
		if (list!=null && list.size()>0) {
			return Const.AJAX_RESULT_TRUE;
		}
		return Const.AJAX_RESULT_FALSE;
	}

	public String getBoDatabyModal(String lang, String params) {
		
		logger.finer("getBoDatabyModal params=="+params);
		
		JSONObject json = null;
		if(!"".equals(params) && params != null){
			json = JSONObject.parseObject(params);
		}
		int fInd = json.getIntValue("startNum");
		List<Map<String,?>> list = boDao.getBoDatabyModal(getSqlStr(json,"data"));
		int total = boDao.getBoDatabyModalTotal(getSqlStr(json,"total"));
		
		String[] cols = json.getString("columns").split(",");
		
		for(int i = 0; i<cols.length; i++){//String col : cols
			if(cols[i].startsWith(fmdProperties.getConfDeftab()+".")){
				cols[i] = cols[i].substring(cols[i].indexOf(".")+1, cols[i].length());
				cols[i] = cols[i] + "__";
			}else{
				cols[i] = cols[i].substring(cols[i].indexOf(".")+1, cols[i].length());
			}
		}
		
		String rtn= DhxUtil.toGridJsonStrNoZone(list, cols, fInd, total);
		//为结果数据添加点击事件
		rtn = rePackDataClick(rtn);
		logger.finer("getBoDatabyModal returns:\n"+rtn);
		
		return rtn;
	}
	
	public String getSqlStr(JSONObject json,String type){
		StringBuilder sqlstr = new StringBuilder();//适合于单线程，速度最快
		if("data".equals(type)){
			//添加按 申请时间倒序排列
			sqlstr.append(" select * from (select rownum rn,a.* from ( select ");
			String[] cols = json.getString("columns").split(",");
			String colstr = "";
			for(int i = 0; i<cols.length; i++){//String col : cols
				if(cols[i].startsWith(fmdProperties.getConfDeftab()+".")){
					String orv = cols[i];
					if(cols[i].equals(fmdProperties.getConfDeftab()+".STATUS")){
						cols[i] = " case when "+fmdProperties.getConfDeftab()+".STATUS = '10' then '审批中' when "+fmdProperties.getConfDeftab()+".STATUS = '20' then '通过' when "
								+fmdProperties.getConfDeftab()+ ".STATUS = '30' then '已取消' when "
										+ fmdProperties.getConfDeftab()+".STATUS = '21' then '已拒绝' when "
												+ fmdProperties.getConfDeftab()+".STATUS = '11' then '暂停' end STATUS__ ";
					}else{
						cols[i] = cols[i].substring(cols[i].indexOf(".")+1, cols[i].length());
						cols[i] = orv+" "+cols[i] + "__";
					}
				}
				colstr += cols[i]+",";
			}
			colstr = colstr.substring(0, colstr.length() - 1);
			sqlstr.append(colstr);
			
			sqlstr.append(" from ");
			String boname = json.getString("boname");
			sqlstr.append(boname+" "+boname+","+fmdProperties.getConfDeftab()+" TS_INST");
			sqlstr.append(" where "+boname+".TS_ID=TS_INST.ID");
			sqlstr.append(" and TS_INST.STATUS not in ('00','01') ");
			
			//查询条件
			if(!json.getJSONArray("queryCondition").isEmpty() &&
					json.getJSONArray("queryCondition").size()>0){
				JSONArray manuCon = json.getJSONArray("queryCondition");
				for(int i = 0; i<manuCon.size(); i++){
					JSONObject jo = manuCon.getJSONObject(i);
					String cond = "";
					if(jo.containsKey("cond")){
						cond = jo.getString("cond");
					}
					Iterator<Map.Entry<String, Object>> it = jo.entrySet().iterator();
					while(it.hasNext()){
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
						
						if("type".equals(entry.getKey()) || "cond".equals(entry.getKey())){
							continue;
						}
						if(!"".equals(entry.getValue())){
							//查询字段的配置类型：string、text、int、number、datetime
							if("string".equals(jo.getString("type")) || "text".equals(jo.getString("type"))){
								
								if(entry.getValue() instanceof JSONObject){//字符串类型 转换为 日期类型的查询条件
									JSONObject durDate = (JSONObject)entry.getValue();
									String startDate = durDate.getString("startDate");
									if(!"".equals(startDate) && startDate != null){
										sqlstr.append(" and ");
										String endDate = durDate.getString("endDate");
										sqlstr.append(" to_date("+entry.getKey()+",'yyyy-mm-dd hh24:mi:ss') between ");
										sqlstr.append(" to_date('"+startDate+"','yyyy-mm-dd hh24:mi:ss') and ");
										sqlstr.append(" to_date('"+endDate+"','yyyy-mm-dd hh24:mi:ss') ");
									}
									
									
								}else if(entry.getValue() instanceof String){
									sqlstr.append(" and ");
									if("eq".equals(cond)){
										sqlstr.append(entry.getKey()+"='"+entry.getValue()+"'");
									}else if("like".equals(cond)){
										sqlstr.append(entry.getKey()+" like '%"+entry.getValue()+"%'");
									}else if("notlike".equals(cond)){
										sqlstr.append(entry.getKey()+" not like '%"+entry.getValue()+"%'");
									}
								}
							}else if("int".equals(jo.getString("type")) || "number".equals(jo.getString("type"))){
								sqlstr.append(" and ");
								if("eq".equals(cond)){
									sqlstr.append(entry.getKey()+"="+entry.getValue());
								}else if("lt".equals(cond)){
									sqlstr.append(entry.getKey()+"<"+entry.getValue());
								}else if("gt".equals(cond)){
									sqlstr.append(entry.getKey()+">"+entry.getValue());
								}else if("lteq".equals(cond)){
									sqlstr.append(entry.getKey()+"<="+entry.getValue());
								}else if("gteq".equals(cond)){
									sqlstr.append(entry.getKey()+">="+entry.getValue());
								}
							}else if("datetime".equals(jo.getString("type"))){
								
								JSONObject durDate = (JSONObject)entry.getValue();
								String startDate = durDate.getString("startDate");
								if(!"".equals(startDate) && startDate != null){
									sqlstr.append(" and ");
									String endDate = durDate.getString("endDate");
									sqlstr.append(entry.getKey()+" between ");
									sqlstr.append(" to_date('"+startDate+"','yyyy-mm-dd hh24:mi:ss') and ");
									sqlstr.append(" to_date('"+endDate+"','yyyy-mm-dd hh24:mi:ss') ");
								}
							}
						}
						
					}
				}
			}
			
			sqlstr.append(" order by TS_INST.create_time desc ");
			
			//排序条件
			if(!json.getJSONObject("orderByConf").isEmpty() &&
					json.getJSONObject("orderByConf")!=null){
				JSONObject orderBys = json.getJSONObject("orderByConf");
				Iterator<Map.Entry<String, Object>> it = orderBys.entrySet().iterator();
				if(it.hasNext()){
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
					sqlstr.append(","+entry.getKey()+" "+entry.getValue());
				}
				
			}
			sqlstr.append(" ) a where rownum <= ");
			sqlstr.append(json.getIntValue("endNum"));
			sqlstr.append(") where rn > ");
			sqlstr.append(json.getIntValue("startNum"));
			
		}else if("total".equals(type)){
			sqlstr.append(" select count(1) ");
			
			sqlstr.append(" from ");
			String boname = json.getString("boname");
			sqlstr.append(boname+" "+boname+","+fmdProperties.getConfDeftab()+" TS_INST");
			sqlstr.append(" where "+boname+".TS_ID=TS_INST.ID");
			sqlstr.append(" and TS_INST.STATUS not in ('00','01') ");
			
			//查询条件
			if(!json.getJSONArray("queryCondition").isEmpty() &&
					json.getJSONArray("queryCondition").size()>0){
				JSONArray manuCon = json.getJSONArray("queryCondition");
				for(int i = 0; i<manuCon.size(); i++){
					JSONObject jo = manuCon.getJSONObject(i);
					String cond = "";
					if(jo.containsKey("cond")){
						cond = jo.getString("cond");
					}
					Iterator<Map.Entry<String, Object>> it = jo.entrySet().iterator();
					while(it.hasNext()){
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
						
						if("type".equals(entry.getKey()) || "cond".equals(entry.getKey())){
							continue;
						}
						if(!"".equals(entry.getValue())){
							//查询字段的配置类型：string、text、int、number、datetime
							if("string".equals(jo.getString("type")) || "text".equals(jo.getString("type"))){
								
								if(entry.getValue() instanceof JSONObject){//字符串类型 转换为 日期类型的查询条件
									JSONObject durDate = (JSONObject)entry.getValue();
									String startDate = durDate.getString("startDate");
									if(!"".equals(startDate) && startDate != null){
										sqlstr.append(" and ");
										String endDate = durDate.getString("endDate");
										sqlstr.append(" to_date("+entry.getKey()+",'yyyy-mm-dd hh24:mi:ss') between ");
										sqlstr.append(" to_date('"+startDate+"','yyyy-mm-dd hh24:mi:ss') and ");
										sqlstr.append(" to_date('"+endDate+"','yyyy-mm-dd hh24:mi:ss') ");
									}
									
									
								}else if(entry.getValue() instanceof String){
									sqlstr.append(" and ");
									if("eq".equals(cond)){
										sqlstr.append(entry.getKey()+"='"+entry.getValue()+"'");
									}else if("like".equals(cond)){
										sqlstr.append(entry.getKey()+" like '%"+entry.getValue()+"%'");
									}else if("notlike".equals(cond)){
										sqlstr.append(entry.getKey()+" not like '%"+entry.getValue()+"%'");
									}
								}
							}else if("int".equals(jo.getString("type")) || "number".equals(jo.getString("type"))){
								sqlstr.append(" and ");
								if("eq".equals(cond)){
									sqlstr.append(entry.getKey()+"="+entry.getValue());
								}else if("lt".equals(cond)){
									sqlstr.append(entry.getKey()+"<"+entry.getValue());
								}else if("gt".equals(cond)){
									sqlstr.append(entry.getKey()+">"+entry.getValue());
								}else if("lteq".equals(cond)){
									sqlstr.append(entry.getKey()+"<="+entry.getValue());
								}else if("gteq".equals(cond)){
									sqlstr.append(entry.getKey()+">="+entry.getValue());
								}
							}else if("datetime".equals(jo.getString("type"))){
								
								JSONObject durDate = (JSONObject)entry.getValue();
								String startDate = durDate.getString("startDate");
								if(!"".equals(startDate) && startDate != null){
									sqlstr.append(" and ");
									String endDate = durDate.getString("endDate");
									sqlstr.append(entry.getKey()+" between ");
									sqlstr.append(" to_date('"+startDate+"','yyyy-mm-dd hh24:mi:ss') and ");
									sqlstr.append(" to_date('"+endDate+"','yyyy-mm-dd hh24:mi:ss') ");
								}
							}
						}
						
					}
				}
			}
			
		}
		
		logger.finer("getSqlStr sqlstr == " + sqlstr.toString());
		
		return sqlstr.toString();
	}

	public String getBoSubDatabyModal(String lang, String params) {
		logger.finer("getBoSubDatabyModal params=="+params);
		
		JSONObject json = null;
		if(!"".equals(params) && params != null){
			json = JSONObject.parseObject(params);
		}
		int fInd = json.getIntValue("startNum");
		List<Map<String,?>> list = boDao.getBoDatabyModal(getSubSqlStr(json,"data"));
		int total = boDao.getBoDatabyModalTotal(getSubSqlStr(json,"total"));
		
		String[] cols = json.getString("columns").split(",");
		
		String rtn= DhxUtil.toGridJsonStrNoZone(list, 
				cols, fInd, total);
		
		logger.finer("getBoSubDatabyModal returns:\n"+rtn);
		return rtn;
	}
	
	public String getSubSqlStr(JSONObject json,String type){
		StringBuilder sqlstr = new StringBuilder();
		if("data".equals(type)){
			sqlstr.append(" select * from ( select rownum rn,");
			sqlstr.append(json.getString("columns"));
		}else if("total".equals(type)){
			sqlstr.append(" select count(1) ");
		}
		
		sqlstr.append(" from ");
		
		String[] subtabs = json.getString("boname").split(",");
		String tabnames = "";
		String joinCondition = "";
		
		for(int i = 0 ;i < subtabs.length;i++){
			tabnames += subtabs[i] + " " + subtabs[i] + ",";
			if(subtabs.length > 1 && i > 0){
				joinCondition += " and " + subtabs[0] + ".TS_ID =" + subtabs[i] + ".TS_ID ";
			}
		}
		tabnames = tabnames.substring(0, tabnames.length() -1 );
		
		sqlstr.append(tabnames);
		
		sqlstr.append(" where ");
		if(!"".equals(json.getString("tsId")) && json.getString("tsId").length() > 0){
			sqlstr.append(subtabs[0]+".TS_ID ='"+json.getString("tsId")+"'");
		}else{
			sqlstr.append("1=1 ");
		}
		if(subtabs.length > 1){
			sqlstr.append(joinCondition);
		}
		
		//查询条件
		if(!json.getJSONArray("queryCondition").isEmpty() &&
				json.getJSONArray("queryCondition").size()>0){
			JSONArray manuCon = json.getJSONArray("queryCondition");
			for(int i = 0; i<manuCon.size(); i++){
				JSONObject jo = manuCon.getJSONObject(i);
				Iterator<Map.Entry<String, Object>> it = jo.entrySet().iterator();
				while(it.hasNext()){
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
					if("type".equals(entry.getKey())){
						continue;
					}
					if(!"".equals(entry.getValue())){
						if("string".equals(jo.getString("type")) || "text".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+" like '%"+entry.getValue()+"%'");
						}else if("int".equals(jo.getString("type")) || "number".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+"="+entry.getValue());
						}else if("datetime".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+"="+entry.getValue());
							
						}
					}
					
				}
			}
		}
		
		if("data".equals(type)){
			sqlstr.append(" and rownum <=" +json.getIntValue("endNum"));
			//排序条件
			if(!json.getJSONObject("orderByConf").isEmpty() &&
					json.getJSONObject("orderByConf")!=null){
				JSONObject orderBys = json.getJSONObject("orderByConf");
				if(!orderBys.isEmpty()){
					Iterator<Map.Entry<String, Object>> it = orderBys.entrySet().iterator();
					if(it.hasNext()){
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
						sqlstr.append(" order by ");
						sqlstr.append(entry.getKey()+" "+entry.getValue()+",");
					}
				}
				
				sqlstr.deleteCharAt(sqlstr.length()-1);
			}
			sqlstr.append(" ) a where a.rn > ");
			sqlstr.append(json.getIntValue("startNum"));
		}
		
		logger.finer("getSubSqlStr sqlstr == " + sqlstr.toString());
		return sqlstr.toString();
	}

	/**
	 * 获取数据字典值
	 * @see com.sf.bpm.baseInfo.service.BdService#getBdDict(java.lang.String, java.lang.String)
	 */
	public List<Map> getBdDict(Map params) {
		return boDao.getBdDict(params);
	}

	public String getOnesubBoData(String lang, String params) {
		logger.finer("getOnesubBoData params=="+params);
		
		JSONObject json = null;
		if(!"".equals(params) && params != null){
			json = JSONObject.parseObject(params);
		}
		int fInd = json.getIntValue("startNum");
		int timezoneOffset = json.getIntValue("timezoneOffset");
		List<Map<String,?>> list = boDao.getBoDatabyModal(getOSubSqlStr(json,"data"));
		int total = boDao.getBoDatabyModalTotal(getOSubSqlStr(json,"total"));
		
		String[] cols = json.getString("columns").split(",");
		for(int i = 0; i<cols.length; i++){//String col : cols
			cols[i] = cols[i].substring(cols[i].indexOf(".")+1, cols[i].length());
		}
		String rtn= DhxUtil.toGridJsonStr(list, 
				cols, fInd+1, total, timezoneOffset, lang);
		
		logger.finer("getOnesubBoData returns:\n"+rtn);
		return rtn;
	}
	
	public String getOSubSqlStr(JSONObject json,String type){
		StringBuilder sqlstr = new StringBuilder();
		if("data".equals(type)){
			sqlstr.append(" select * from ( select rownum rn,");
			sqlstr.append(json.getString("columns"));
		}else if("total".equals(type)){
			sqlstr.append(" select count(1) ");
		}
		
		sqlstr.append(" from ");
		
		sqlstr.append(json.getString("boname"));
		
		sqlstr.append(" where TS_ID = '"+json.getString("tsId")+"'");
		
		//查询条件
		if(!json.getJSONArray("queryCondition").isEmpty() &&
				json.getJSONArray("queryCondition").size()>0){
			JSONArray manuCon = json.getJSONArray("queryCondition");
			for(int i = 0; i<manuCon.size(); i++){
				JSONObject jo = manuCon.getJSONObject(i);
				Iterator<Map.Entry<String, Object>> it = jo.entrySet().iterator();
				while(it.hasNext()){
					Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
					if("type".equals(entry.getKey())){
						continue;
					}
					if(!"".equals(entry.getValue())){
						if("string".equals(jo.getString("type")) || "text".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+" like '%"+entry.getValue()+"%'");
						}else if("int".equals(jo.getString("type")) || "number".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+"="+entry.getValue());
						}else if("datetime".equals(jo.getString("type"))){
							sqlstr.append(" and ");
							sqlstr.append(entry.getKey()+"="+entry.getValue());
							
						}
					}
					
				}
			}
		}
		
		if("data".equals(type)){
			sqlstr.append(" and rownum <=" +json.getIntValue("endNum"));
			//排序条件
			if(!json.getJSONObject("orderByConf").isEmpty() &&
					json.getJSONObject("orderByConf")!=null){
				JSONObject orderBys = json.getJSONObject("orderByConf");
				if(!orderBys.isEmpty()){
					Iterator<Map.Entry<String, Object>> it = orderBys.entrySet().iterator();
					if(it.hasNext()){
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>)it.next();
						sqlstr.append(" order by ");
						sqlstr.append(entry.getKey()+" "+entry.getValue()+",");
					}
				}
				
				sqlstr.deleteCharAt(sqlstr.length()-1);
			}
			sqlstr.append(" ) a where a.rn > ");
			sqlstr.append(json.getIntValue("startNum"));
		}
		
		logger.finer("getOSubSqlStr sqlstr == " + sqlstr.toString());
		return sqlstr.toString();
	}
	
	public String rePackDataClick(String data){
		JSONObject json = JSONObject.parseObject(data);
		
		JSONArray arr = json.getJSONArray("rows");
		
		Iterator<Object> it = arr.iterator();
		while (it.hasNext()) {
			JSONObject js = (JSONObject)it.next();
			JSONArray jsda = js.getJSONArray("data");
			
			for (int i = 0; i < jsda.size(); i++) {
				
				if(jsda.get(i)!=null  && !"".equals(jsda.get(i)) && i != 1){
					jsda.set(i, "<a style='color:black;text-decoration: none;' href=\"#\" onclick=\"openFormByTsid(" + js.getIntValue("id") + ")\">" + jsda.get(i) + "</a>");
				}else if(jsda.get(i)==null){
					jsda.set(i,"");
				}
			}
		}
		
		return json.toString();
	}
	
}
